package poipoi;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Main {

	public static void main(String[] args) throws FileNotFoundException, IOException {
		// TODO Auto-generated method stub
	    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream("src/poipoi/test.xlsx"));
		XSSFSheet sheet = wb.getSheetAt(0);
		
		//读取excel
		Iterator<Row> rows = sheet.rowIterator(); //获得第一个表单的迭代器
        while (rows.hasNext()) {
            Row row = rows.next();  //获得行数据  
            System.out.println("Row #" + row.getRowNum());  //获得行号从0开始  
            Iterator<Cell> cells = row.cellIterator();    //获得第一行的迭代器  
            while (cells.hasNext()) {  
                Cell cell = cells.next();  
                System.out.print("Cell #" + cell.getColumnIndex());
                System.out.print(cell.getStringCellValue());
            }
            System.out.println();
        }
        
        //设置cell format
        XSSFSheet newsheet = wb.createSheet("Test");
        XSSFRow row = newsheet.createRow(0);
        XSSFCell firstcell = row.createCell(0);
        firstcell.setCellValue(1234.56789);
        XSSFCellStyle style = wb.createCellStyle();
        style.setDataFormat(wb.createDataFormat().getFormat("￥#,##0"));
        firstcell.setCellStyle(style);
           
        //合并单元格
        XSSFCell secondcell = row.createCell(1);
        secondcell.setCellValue("合并列");
        CellRangeAddress region = new CellRangeAddress(0, 0, 1, 5);
        newsheet.addMergedRegion(region);
        
        //改变对齐方式
        XSSFCell thirdcell = row.createCell(6);
        thirdcell.setCellValue("单元格对齐");
        XSSFCellStyle secondstyle = wb.createCellStyle();
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中
        thirdcell.setCellStyle(style);
        
        //添加函数
        XSSFRow newrow = newsheet.createRow(1);
        newrow.createCell(0).setCellValue(7);//A1
        newrow.createCell(1).setCellValue(8);//B1
        XSSFCell cell = newrow.createCell(2);
        cell.setCellFormula("A2 * B2 + 14");
        XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(wb);
        cell = e.evaluateInCell(cell);//若Excel文件不是POI创建的，则不必调用此方法
        
        //工作簿保存
        FileOutputStream fos = new FileOutputStream("src/poipoi/new.xlsx");
        wb.write(fos);
        fos.close();
        
	}
}
